﻿<cfsilent>
	<cfsetting requesttimeout="300" />
	<cfflush interval="1024" />
	<cfscript>

		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		propAdvice = getProperty("serviceFactory").getBean("schemaPropertyAdvice");
		sequenceGraduateCondition = getProperty("serviceFactory").getBean("sequenceGraduateCondition");
		sequenceProgramSchema = getProperty("serviceFactory").getBean("sequenceProgramSchema");
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		schemaUpdate = 0;
		schemaInsert = 0;

		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labImport", true);
	
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入课程数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>

		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="培养方案">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入课程信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>
				
				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />

				<cfif not listFindNoCase(rs.columnList, "专业")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "年级")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "课程号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "课程")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "是否必修")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "课程类别")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "开课学期")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "任课单位")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 专业, 年级, 课程号, 课程, 是否必修, 课程类别, 开课学期, 任课单位</p>
				</cfif>
				
				<cfif columnChecked>
				
					<p class="success">正在检查数据子项 ... 完成</p>
					
					<cftransaction>
						
						<cfloop query="rs">

							<!--- 检查专业名称是否正确 --->
                            <cfset sql = "SELECT sbj_id, term_start, sbj_name, sbj_direction 
											FROM t_subject 
											WHERE sbj_name = :sbjName " />
                            <cfset queryObj = new query( datasource=application.dnsMaster ) />
                            <cfset queryObj.addParam( name="sbjName", value=rs["专业"][rs.currentRow], cfsqltype="cf_sql_varchar" ) />
                            <cfset rs_subjectCheck = queryObj.execute( sql=sql ).getResult() /> 
                           							
							<cfif rs_subjectCheck.recordCount eq 1>

									<cfset grade = rs["年级"][rs.currentRow] />
									
									<!--- 专业检查无误 设置该专业年级的毕业条件 --->
                                    <cfset sequenceGraduateCondition.setgrade(grade) />
                                    <cfset sequenceGraduateCondition.setsubjectId(rs_subjectCheck.sbj_id) />
                                    <cfset id = sequenceGraduateCondition.getID() />
									
									<cfset sql = "SELECT 1 
													FROM t_condition 
													WHERE con_id = :conditionId " />
									<cfset queryObj = new query( datasource=application.dnsMaster ) />
									<cfset queryObj.addParam( name="conditionId", value=id, cfsqltype="cf_sql_varchar" ) />
									<cfset rs_conditionExist = queryObj.execute( sql=sql ).getResult() /> 
							
									<cfif rs_conditionExist.recordCount EQ 0>
									
										<!--- 秋季招生 则建班为9月 春季招生为3月 --->
										<cfif rs_subjectCheck.term_start eq "1">
                                        		<cfset schemaFound = grade & "09" />
											<cfelse>
                                            	<cfset schemaFount = grade & "03" />
										</cfif>
										<!--- 学分学时要求都暂时设置为0 --->
								
                                        <cfset sql = "INSERT INTO t_condition 	
																 (
																	con_id,
																	grade,
																	sbj_id,
																	credit,
																	credit_required,
																	credit_optional,
																	credit_relish,
																	period,
																	period_required,
																	period_optional,
																	found_date 
																 )
																VALUES(
																	:conditionId, 
																	:grade, 
																	:subjectId, 
																	:credit, 
																	:creditRequired, 
																	:creditOptional, 
																	:creditRelish, 
																	:period, 
																	:periodRequired, 
																	:periodOptional, 
																	:foundDate 
																)" />
										<cfset queryObj = new query( datasource=application.dnsMaster ) />
										<cfset queryObj.addParam( name="conditionId", value=id, cfsqltype="cf_sql_varchar" ) />
                                        <cfset queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" ) />
                                        <cfset queryObj.addParam( name="subjectId", value=rs_subjectCheck.sbj_id, cfsqltype="cf_sql_varchar" ) />
                                        <cfset queryObj.addParam( name="credit", value=0, cfsqltype="cf_sql_decimal", scale=1 ) />
                                        <cfset queryObj.addParam( name="creditRequired", value=0, cfsqltype="cf_sql_decimal", scale=1 ) />
                                        <cfset queryObj.addParam( name="creditOptional", value=0, cfsqltype="cf_sql_decimal", scale=1 ) />
                                        <cfset queryObj.addParam( name="creditRelish", value=0, cfsqltype="cf_sql_decimal", scale=1 ) />
                                        <cfset queryObj.addParam( name="period", value=0, cfsqltype="cf_sql_integer" ) />
                                        <cfset queryObj.addParam( name="periodRequired", value=0, cfsqltype="cf_sql_integer" ) />
                                        <cfset queryObj.addParam( name="periodOptional", value=0, cfsqltype="cf_sql_integer" ) />
                                        <cfset queryObj.addParam( name="foundDate", value=schemaFound, cfsqltype="cf_sql_char" ) />
										<cfset queryObj.execute( sql=sql )/> 
						
										<p class="success">设置培养方案版本数据 ... <cfoutput>#grade#</cfoutput>版-<cfoutput>#rs_subjectCheck.sbj_name#</cfoutput>专业培养方案 ... 完成</p>
									</cfif>
									
									<!--- 检查课程名称及任课单位是否正确 --->
                                    <cfset sql = "SELECT cid  
													FROM t_course 
													WHERE course_code = :courseCode " />
									<cfset queryObj = new query( datasource=application.dnsMaster ) />
									<cfset queryObj.addParam( name="courseCode", value=rs["课程号"][rs.currentRow], cfsqltype="cf_sql_varchar" ) />
									<cfset rs_courseCheck = queryObj.execute( sql=sql ).getResult() /> 
                   
									<cfif rs_courseCheck.recordCount eq 1>
		
											
											<!--- 课程 专业 年级 检查无误 下一步检查该培养方案记录是否存在 --->
											<cfset sequenceProgramSchema.setSubjectID(rs_subjectCheck.sbj_id) />
                                            <cfset sequenceProgramSchema.setGrade(grade) />
                                            <cfset sequenceProgramSchema.setSubjectDirection(rs_subjectCheck.sbj_direction) />
                                            <cfset sequenceProgramSchema.setCourseID(rs_courseCheck.cid) />
                                            <cfset id = sequenceProgramSchema.getID() />
																			
											<cfset sql = "SELECT shm_prop, grade   
															FROM t_schema  
															WHERE shm_id = :schemaId " />
											<cfset queryObj = new query( datasource=application.dnsMaster ) />
											<cfset queryObj.addParam( name="schemaId", value=id, cfsqltype="cf_sql_varchar" ) />
											<cfset rs_schemaEntityExist = queryObj.execute( sql=sql ).getResult() /> 
									
											<cfif rs_schemaEntityExist.recordCount EQ 0>
													<!--- 创建新的专业开课数据 --->
                                            
													<cfset propAdvice = propAdvice.init() />
													
												<cfelse>
                                                	<!---更新grade，底下调用--->
                                                	<cfset grade = rs_schemaEntityExist.grade />
													<!--- 更新现有专业开课数据 --->
													
													<cfset propAdvice.parseProperty( rs_schemaEntityExist.shm_prop ) />
											</cfif>
											
											<!--- 设置课程必修标志 --->
											<cfif rs["是否必修"][rs.currentRow] eq "必修">
                                            		<cfset propId = 1 />
												<cfelse>
                                                	<cfset propId = 2 />
											</cfif>
											<!--- 设置课程类别 --->
											<cfif rs["课程类别"][rs.currentRow] eq "公共课">
                                            		<cfset cnaId = 1 />
												<cfelseif rs["课程类别"][rs.currentRow] eq "实验课">
                                               		<cfset cnaId = 3 />
												<cfelseif rs["课程类别"][rs.currentRow] eq "实践课">
                                                	<cfset cnaId = 4 />
												<cfelseif rs["课程类别"][rs.currentRow] eq "理论课">
                                                	<cfset cnaId = 5 />
												<cfelseif rs["课程类别"][rs.currentRow] eq "其他">
                                                	<cfset cnaId = 9 />
												<cfelse>
                                                	<cfset cnaId = 2 />
											</cfif>
                                            
											<!--- 可选数据项 --->
											
											
											<cfif listFindNoCase(rs.columnList, "学位课") and ( rs["学位课"][rs.currentRow] eq "是" or rs["学位课"][rs.currentRow] eq "否" )>
												<cfif rs["学位课"][rs.currentRow] eq "是">
														<cfset propAdvice.setDegreeCourse( true )  />
													<cfelse>
														<cfset propAdvice.setDegreeCourse( false )  />
												</cfif>
											</cfif>
											<cfif listFindNoCase(rs.columnList, "双学位") and ( rs["双学位"][rs.currentRow] eq "是" or rs["双学位"][rs.currentRow] eq "否" )>
												<cfif rs["双学位"][rs.currentRow] eq "是">
														<cfset propAdvice.setSecondDegree( true )  />
													<cfelse>
														<cfset propAdvice.setSecondDegree( false )  />
												</cfif>
											</cfif>
											<cfif listFindNoCase(rs.columnList, "辅修") and ( rs["辅修"][rs.currentRow] eq "是" or rs["辅修"][rs.currentRow] eq "否" )>
												<cfif rs["辅修"][rs.currentRow] eq "是">
														<cfset propAdvice.setAssistCourse( true )  />
													<cfelse>
														<cfset propAdvice.setAssistCourse( false )  />
												</cfif>
											</cfif>
											
											<!--- 尝试将专业开课数据保存至数据库 --->
											<cftry>
												
												<cfif isNumeric(rs["开课学期"][rs.currentRow])>
                                                		<cfset termIndex = numberFormat(rs["开课学期"][rs.currentRow], '0_') />
													<cfelse>
                                                    	<cfset termIndex = rs["开课学期"][rs.currentRow] />
												</cfif>
												
												<!--- 转换学期序列到学年学期 --->
												<cfset academicTermYear = mathAdvice.convertTermIdxToAcademicTermYear(termIndex, grade, rs_subjectCheck.term_start) />
                                                
                                                <cfif rs_schemaEntityExist.recordCount EQ 0>
														<!---插入一条数据--->
                                                        <cfset sql = "INSERT INTO t_schema 	
																				 (
																					shm_id,
																					grade,
																					sbj_id,
																					cid,
																					term_index,
																					prop_id,
																					cna_id,
																					academic_year,
																					academic_term,
																					shm_prop 
																				)
																				VALUES(
																					:schemaId, 
																					:grade, 
																					:subjectId, 
																					:courseId, 
																					:termIndex, 
																					:propId, 
																					:cnaId, 
																					:academicYear, 
																					:academicTerm, 
																					:shm_prop 
																				)" />
														<cfset queryObj = new query( datasource=application.dnsMaster ) />
                                                        
														<cfset queryObj.addParam( name="schemaId", value=id, cfsqltype="cf_sql_varchar" ) />
														<cfset queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" ) />
														<cfset queryObj.addParam( name="subjectId", value=rs_subjectCheck.sbj_id, cfsqltype="cf_sql_varchar" ) />
														<cfset queryObj.addParam( name="courseId", value=rs_courseCheck.cid, cfsqltype="cf_sql_varchar" ) />
														<cfset queryObj.addParam( name="termIndex", value=termIndex, cfsqltype="cf_sql_char"  ) />
														<cfset queryObj.addParam( name="propId", value=propId, cfsqltype="cf_sql_char" ) />
														<cfset queryObj.addParam( name="cnaId", value=cnaId, cfsqltype="cf_sql_char"  ) />
														<cfset queryObj.addParam( name="academicYear", value=academicTermYear[1], cfsqltype="cf_sql_char" ) />
														<cfset queryObj.addParam( name="academicTerm", value=academicTermYear[2], cfsqltype="cf_sql_char" ) />
														<cfset queryObj.addParam( name="shm_prop", value=propAdvice.stringProperty(), cfsqltype="cf_sql_varchar" ) />
														<cfset queryObj.execute( sql=sql )/> 
                                                    <cfelse>
                                                    	<!---更新--->
                                                       	<cfset sql = "UPDATE t_schema 	
																			SET 
																				  term_index = :termIndex,
																				  prop_id = :propId,
																				  cna_id = :cnaId,
																				  academic_year = :academicYear,
																				  academic_term = :academicTerm,
																				  shm_prop = :shm_prop 
																			WHERE 
																				  shm_id = :schemaId " />
														<cfset queryObj = new query( datasource=application.dnsMaster ) />
                                                        
														<cfset queryObj.addParam( name="schemaId", value=id, cfsqltype="cf_sql_varchar" ) />
														<cfset queryObj.addParam( name="termIndex", value=termIndex, cfsqltype="cf_sql_char"  ) />
														<cfset queryObj.addParam( name="propId", value=propId, cfsqltype="cf_sql_char" ) />
														<cfset queryObj.addParam( name="cnaId", value=cnaId, cfsqltype="cf_sql_char"  ) />
														<cfset queryObj.addParam( name="academicYear", value=academicTermYear[1], cfsqltype="cf_sql_char" ) />
														<cfset queryObj.addParam( name="academicTerm", value=academicTermYear[2], cfsqltype="cf_sql_char" ) />
														<cfset queryObj.addParam( name="shm_prop", value=propAdvice.stringProperty(), cfsqltype="cf_sql_varchar" ) />
														<cfset queryObj.execute( sql=sql )/> 
                                                </cfif>
                                            
												<cfif rs_schemaEntityExist.recordCount EQ 0>
														<!--- 创建新的专业开课数据 --->
														<cfset schemaInsert ++ />
														<cfset ormflush() />
													<cfelse>
														<!--- 更新现有专业开课数据 --->
														<cfset schemaUpdate ++ />
												</cfif>
										
												<cfcatch type="any">
													<p class="error"><cfoutput>#grade#</cfoutput>版-<cfoutput>#rs_subjectCheck.sbj_name#</cfoutput>专业培养方案 (<cfoutput>#rs["课程"][rs.currentRow]#</cfoutput>) 任课单位 (<cfoutput>#rs["任课单位"][rs.currentRow]#</cfoutput>) 相关信息有误. 课程信息未能录入</p> 
												</cfcatch>
											</cftry>
											
											
										<cfelse>
											<p class="error"><cfoutput>#rs["课程"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["课程号"][rs.currentRow]#</cfoutput>) 任课单位 <cfoutput>#rs["任课单位"][rs.currentRow]#</cfoutput> 录入不正确. 该课程信息在系统中没有对应的数据记录.</p> 
									</cfif>

								<cfelse>
									<p class="error">(<cfoutput>#rs["专业"][rs.currentRow]#</cfoutput>) 录入不正确. 该专业信息在系统中没有对应的数据记录.</p> 
							</cfif>
							
						</cfloop>
						
					</cftransaction>
					
				
				</cfif>
			</cfif>
		</cfif>
	
		<p>新增课程开设数据 [<cfoutput>#schemaInsert#</cfoutput>] 条, 更新现有课程开设数据 [<cfoutput>#schemaUpdate#</cfoutput>] 条.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL('programSchema')#</cfoutput>">刷新培养方案</a>			
	
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>